/** Copyright 2014, Ross Mahony. Some rights reserved. See COPYING. */
package models

import java.util.Date
import java.sql.{ Date => SqlDate }

import play.api.db.slick.Config.driver.simple._
import scala.slick.lifted.Tag

case class Roadmap(customerId: Long, level: String, budget: Double, generated: Date, updated: Option[Date])
case class RoadmapMeasure(roadmapId: Option[Long], roadmapMeasureId: Long, status: String, savingCost: Double, 
  installCost: Double, savingCO2: Double)
case class RoadmapScheme(roadmapId: Long, schemeId: Long, score: Double)
case class RoadmapDiscount(roadmapId: Option[Long], name: String, discount: Double, note: String)

/** Roadmap entity. */
class RoadmapTbl(tag: Tag) extends Table[Roadmap](tag, "RD_MAP") {
  implicit val dateColumnType = MappedColumnType.base[Date, Long](d => d.getTime, d => new Date(d))
  
  def customerId = column[Long]("CUST_ID", O.PrimaryKey)
  def level = column[String]("LVL", O.NotNull)
  def budget = column[Double]("BUDGET", O.NotNull)
  def generated = column[Date]("GEN", O.NotNull)
  def updated = column[Date]("UPD", O.Nullable)
  def * = (customerId, level, budget, generated, updated.?) <> (Roadmap.tupled, Roadmap.unapply _)
}

/** RoadmapMeasure entity. */
class RoadmapMeasureTbl(tag: Tag) extends Table[RoadmapMeasure](tag, "RD_MAP_MSR") {
  def roadmapId = column[Long]("RM_ID", O.NotNull)
  def roadmapMeasureId = column[Long]("MSR_ID", O.NotNull)
  def status = column[String]("STAT", O.NotNull)
  def savingCost = column[Double]("SV_COST", O.NotNull)
  def installCost = column[Double]("INST_COST", O.NotNull)
  def savingCO2 = column[Double]("SV_C02", O.NotNull)
  def * = (roadmapId.?, roadmapMeasureId, status, savingCost, installCost, savingCO2) <> (RoadmapMeasure.tupled, RoadmapMeasure.unapply _)
}

/** RoadmapScheme entity. */
class RoadmapSchemeTbl(tag: Tag) extends Table[RoadmapScheme](tag, "RD_MAP_SCHM") {
  def roadmapId = column[Long]("RM_ID", O.NotNull)
  def schemeId = column[Long]("SCHM_ID", O.NotNull)
  def score = column[Double]("SCR", O.NotNull)
  def * = (roadmapId, schemeId, score) <> (RoadmapScheme.tupled, RoadmapScheme.unapply _)
}

/** RoadmapDiscount entity. */
class RoadmapDiscountTbl(tag: Tag) extends Table[RoadmapDiscount](tag, "RD_MAP_DSCNT") {
  def roadmapId = column[Long]("RM_ID", O.NotNull)
  def name = column[String]("NAME", O.NotNull)
  def discount = column[Double]("DSCNT", O.NotNull)
  def note = column[String]("NOTE", O.NotNull)
  def * = (roadmapId.?, name, discount, note) <> (RoadmapDiscount.tupled, RoadmapDiscount.unapply _)
}

/** Roadmap DAO */
object RoadmapDAO {
  val roadmaps = TableQuery[RoadmapTbl]
  val measures = TableQuery[MeasureTbl]
  val schemes = TableQuery[SchemeTbl]
  val discounts = TableQuery[RoadmapDiscountTbl]
  val roadmapMeasures = TableQuery[RoadmapMeasureTbl]
  val roadmapSchemes = TableQuery[RoadmapSchemeTbl]
  
 /**
  * Retrieve a [[models.Roadmap]] by the customer id
  *
  * @param id
  */
  def findByCustomerId(customerId: Long)(implicit s: Session): Option[Roadmap] = {
    roadmaps.where(_.customerId === customerId).firstOption
  }

 /** Count all roadmaps */
  def count(implicit s: Session): Int =
  Query(roadmaps.length).first

 /**
  * Insert a new [[models.Roadmap]]
  *
  * @param roadmap
  */
  def insert(roadmap: Roadmap)(implicit s: Session) {
    roadmaps.insert(roadmap)
  }

 /**
  * Insert a new [[models.Roadmap]] by ignoring auto inc column
  *
  * @param roadmap
  */
  def forceInsert(roadmap: Roadmap)(implicit s: Session) {
    roadmaps.forceInsert(roadmap)
  }  

 /**
  * Retrieve [[models.Measure]] and [[models.RoadmapMeasure]] by 
  * the roadmap id
  *
  * @param id
  */
  def listMeasures(id: Long)(implicit s: Session): List[(Measure, RoadmapMeasure)] = {
    val query =
      (for {
        (measure, rMeasure) <- measures rightJoin roadmapMeasures on (_.id === _.roadmapMeasureId)
        if (rMeasure.roadmapId === id)
      } yield (measure, rMeasure))

    query.list  
  }

 /**
  * Insert a new [[models.RoadmapMeasure]]
  *
  * @param roadmapMeasure
  */
  def insertMeasure(roadmapMeasure: RoadmapMeasure)(implicit s: Session) {
    roadmapMeasures.insert(roadmapMeasure)
  }

 /**
  * Insert a new [[models.RoadmapMeasure]] by ignoring auto inc column
  *
  * @param roadmapMeasure
  */
  def forceInsertMeasure(roadmapMeasure: RoadmapMeasure)(implicit s: Session) {
    roadmapMeasures.forceInsert(roadmapMeasure)
  }

 /**
  * Retrieve [[models.RoadmapDiscount]] by the roadmap id
  *
  * @param id
  */
  def listDiscounts(roadmapId: Long)(implicit s: Session): List[RoadmapDiscount] = {
    discounts.where(_.roadmapId === roadmapId).list
  }

 /**
  * Insert a new [[models.RoadmapDiscount]]
  *
  * @param discount
  */
  def insertDiscount(discount: RoadmapDiscount)(implicit s: Session) {
    discounts.insert(discount)
  }

 /**
  * Insert a new [[models.RoadmapDiscount]] by ignoring auto inc column
  *
  * @param discount
  */
  def forceInsertDiscount(discount: RoadmapDiscount)(implicit s: Session) {
    discounts.forceInsert(discount)
  }


 /** Delete all schemes, measures, discounts and roadmaps */
  def deleteAll(implicit s: Session) {
    roadmapSchemes.delete
    roadmapMeasures.delete
    measures.delete
    schemes.delete
    discounts.delete
    roadmaps.delete
  }

 /** Delete roadmap measures by roadmap id */
  def deleteMeasures(roadmapId: Long)(implicit s: Session) = {
    roadmapMeasures.where(_.roadmapId === roadmapId).delete
  }

 /** Delete discounts by roadmap id */
  def deleteDiscounts(roadmapId: Long)(implicit s: Session) = {
    discounts.where(_.roadmapId === roadmapId).delete
  }
}
